﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace HRDexCapstoneProject.Functions.Messege
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        DataTable dtbl = new DataTable();
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        protected void Page_Load(object sender, EventArgs e)
        {
            //string msg = Request.QueryString["MessegeSenderID"];
            string msg = (string)(Session["messageID"]);
            string snt = (string)(Session["sentID"]);
            
            if (msg != null)
            {

                string sqlc = "SELECT  formMessegeSender.Subject, formMessegeSender.Description, formMessegeSender.DateTime,  formMessegeSender.AttachmentName," +
                " formEmployees.LastName + ', ' + formEmployees.FirstName AS fulname, catMessege.catMessegeName, formMessegeSender.fromEmpID, formMessegeSender.AttachmentName " +
                "FROM  formMessegeSender INNER JOIN formEmployees ON formMessegeSender.EmployeeID = formEmployees.EmployeeID " + 
                "INNER JOIN catMessege ON formMessegeSender.catMessegeID = catMessege.catMessegeID " + 
                "WHERE (formMessegeSender.MessageID = " + msg + ")";
            
            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            cmd.Fill(dtbl);
            lblFrom.Text = dtbl.Rows[0].ItemArray[3].ToString();
            lblTo.Text = dtbl.Rows[0].ItemArray[5].ToString();
            lblSubject.Text = dtbl.Rows[0].ItemArray[0].ToString();
            lblDescription.Text = dtbl.Rows[0].ItemArray[1].ToString();
            //lblAttach.Text = dtbl.Rows[0].ItemArray[2].ToString();
            lblPubDate.Text = dtbl.Rows[0].ItemArray[2].ToString();
            lblType.Text = dtbl.Rows[0].ItemArray[4].ToString();
            if (dtbl.Rows[0][6].ToString() == null)
            {
                lblAttach.Visible = false;
            }

            }
            else if (true)
            {
                string sqlc = "SELECT  formMessegeSender.Subject, formMessegeSender.Description, formMessegeSender.DateTime,  formMessegeSender.AttachmentName," +
                " formEmployees.LastName + ', ' + formEmployees.FirstName AS fulname, catMessege.catMessegeName, formMessegeSender.EmployeeID, formMessegeSender.AttachmentName " +
                "FROM  formMessegeSender INNER JOIN formEmployees ON formMessegeSender.EmployeeID = formEmployees.EmployeeID " +
                "INNER JOIN catMessege ON formMessegeSender.catMessegeID = catMessege.catMessegeID " +
                "WHERE (formMessegeSender.fromEmpID = " + snt + ")";

                SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

                cmd.Fill(dtbl);
                lblFrom.Text = dtbl.Rows[0].ItemArray[3].ToString();
                lblTo.Text = dtbl.Rows[0].ItemArray[5].ToString();
                lblSubject.Text = dtbl.Rows[0].ItemArray[0].ToString();
                lblDescription.Text = dtbl.Rows[0].ItemArray[1].ToString();
                //lblAttach.Text = dtbl.Rows[0].ItemArray[2].ToString();
                lblPubDate.Text = dtbl.Rows[0].ItemArray[2].ToString();
                lblType.Text = dtbl.Rows[0].ItemArray[4].ToString();
                if (dtbl.Rows[0][6].ToString() == null)
                {
                    lblAttach.Visible = false;
                }
            }
            else
            {
                Panel2.Visible = false;
                Panel1.Visible = true;
            }
        }

        protected void txtSubmit_Click1(object sender, EventArgs e)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            conn.Open();

            SqlCommand cmd = new SqlCommand("Insert Into formMessegeSender (Subject, Description, DateTime, EmployeeID, catMessegeID, fromEmpID) "+
                "Values (@subj, @desc,  @datetime, @empID, @catMsg, @fromEmpID)", conn);
            Stream fs = fuAttachment.PostedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);
 
            cmd.Parameters.Add("@subj", SqlDbType.VarChar).Value = txtSubject.Text;
            cmd.Parameters.Add("@desc", SqlDbType.VarChar).Value = txtBody.Text;
            cmd.Parameters.Add("@datetime", SqlDbType.VarChar).Value = DateTime.Now;
            cmd.Parameters.Add("@empID", SqlDbType.Int).Value = ddlToSend.Text;
            //cmd.Parameters.Add("@attachment", SqlDbType.Binary).Value = bytes;
            cmd.Parameters.Add("@catMsg", SqlDbType.Int).Value = ddlCategory.Text;
            cmd.Parameters.Add("@fromEmpID", SqlDbType.VarChar).Value = SessionID();
    
            
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }

        private void InitializeComponent()
        {
            //this.cmdStore.Click += new System.EventHandler(this.cmdStore_Click);
            this.Load += new System.EventHandler(this.Page_Load);
        }

        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }

        protected void lblAttach_Click(object sender, EventArgs e)
        {
            string msg = (string)(Session["messageID"]);
            string strQuery = "SELECT AttachmentData, AttachmentName FROM formMessegeSender WHERE MessageID = " + msg;
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);
            if (dt != null)
            {
                download(dt);
            }
        }

        private DataTable GetData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                return dt;

            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
                sda.Dispose();
                conn.Dispose();
            }

        }
        private void download(DataTable dt)
        {
            Byte[] bytes = (Byte[])dt.Rows[0]["AttachmentData"];
            Response.Buffer = true;
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            //Response.ContentType = dt.Rows[0]["FileType"].ToString();
            Response.AddHeader("content-disposition", "attachment;FileName="
            + dt.Rows[0]["AttachmentName"].ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }

        protected void btnDelete_Click(object sender, ImageClickEventArgs e)
        {
            string msg = (string)(Session["messageID"]);
            string del = "DELETE FROM formMessegeSender WHERE     (MessageID = " + msg + ")";
            sqlexecute(del);
            Server.Transfer("~/Functions/Messege/ReceivedItems.aspx");
        }
        void sqlexecute(string sqlcmd)
        {
            SqlCommand cmd = new SqlCommand(sqlcmd, conn);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }

        }
    }
}